package de.fhkl.thatsstroke.database;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import de.fhkl.thatsstroke.Inbox;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

/**
 * Data Access Object for Videos stored in the DB.
 */
public class VideoDataSource
{
	/**
	 * Constant for ten minutes in milliseconds
	 */
	public static final int THIRTY_MIN_AS_MS = 30 * 60 * 1000;

	/**
	 * Database object.
	 */
	private SQLiteDatabase mDatabase;

	/**
	 * Database connection helper object.
	 */
	private StrokeSQLiteHelper mDbHelper;

	/**
	 * String array containing all database columns.
	 */
	private String[] mAllColumns = { StrokeSQLiteHelper.COLUMN_ID, StrokeSQLiteHelper.COLUMN_VIDEO_ID,
			StrokeSQLiteHelper.COLUMN_DATE, StrokeSQLiteHelper.COLUMN_FLAG, StrokeSQLiteHelper.COLUMN_PARTS,
			StrokeSQLiteHelper.COLUMN_PROGRESS };

	/**
	 * Constructs a new VideoDataSource.
	 * 
	 * @param context
	 */
	public VideoDataSource(Context context)
	{
		mDbHelper = new StrokeSQLiteHelper(context);
	}

	/**
	 * Create and/or open a database that will be used for reading and writing.
	 * 
	 * @throws SQLException
	 */
	public void open() throws SQLException
	{
		mDatabase = mDbHelper.getWritableDatabase();
	}

	/**
	 * Closes any open database connection.
	 */
	public void close()
	{
		mDbHelper.close();
	}

	/**
	 * Inserts a new video in the database if it does not already exist there.
	 * The check is done by videoId, not the primary key _id, as this is only
	 * valid and unique within the app context.
	 * 
	 * @param id
	 *            UUID of the video
	 * @param date
	 *            Date at which the video was received
	 * @param flag
	 *            Current state of the video rating process
	 * @param parts
	 *            Number of parts this video consists of
	 * @return a new video object holding the data of the insert or null if the
	 *         video was already stored in the database
	 */
	public Video insertVideo(UUID id, Date date, int flag, int parts)
	{
		Log.i("VideoDataSource", "Inserted video: id " + id + " date " + date.getTime() + " is loaded " + flag + " parts "
				+ parts);

		if (containsVideoId(id) != null)
		{
			return null;
		}

		ContentValues values = new ContentValues();

		values.put(StrokeSQLiteHelper.COLUMN_VIDEO_ID, id.toString());
		values.put(StrokeSQLiteHelper.COLUMN_DATE, date.getTime());
		values.put(StrokeSQLiteHelper.COLUMN_FLAG, flag);
		values.put(StrokeSQLiteHelper.COLUMN_PARTS, parts);
		values.put(StrokeSQLiteHelper.COLUMN_PROGRESS, 0);

		long insertId = mDatabase.insert(StrokeSQLiteHelper.TABLE_VIDEOS, null, values);

		Cursor cursor = mDatabase.query(StrokeSQLiteHelper.TABLE_VIDEOS, mAllColumns, StrokeSQLiteHelper.COLUMN_ID + " = "
				+ insertId, null, null, null, null);
		cursor.moveToFirst();
		Video newVideo = cursorToVideo(cursor);
		cursor.close();
		return newVideo;
	}

	/**
	 * Check if the video associated with this UUID is contained by the
	 * database.
	 * 
	 * @param id
	 *            videoId
	 * @return the video if it was contained, null otherwise
	 */
	public Video containsVideoId(UUID id)
	{
		Log.d("VideoDataSource", "containsVideoId video: id " + id.toString());

		Cursor cursor = mDatabase.query(StrokeSQLiteHelper.TABLE_VIDEOS, mAllColumns, StrokeSQLiteHelper.COLUMN_VIDEO_ID + " = '"
				+ id.toString() + "'", null, null, null, null);

		Video video = null;

		if (cursor.getCount() > 0)
		{
			cursor.moveToFirst();

			video = cursorToVideo(cursor);
		}

		cursor.close();

		return video;
	}

	/**
	 * Updates the video rating status flag of the video associated with the
	 * given UUID.
	 * 
	 * @param id
	 *            videoId
	 * @param flag
	 *            rating process status
	 */
	public void updateVideoFlag(UUID id, int flag)
	{
		Log.i("VideoDataSource", "Updated video: id " + id.toString() + " is loaded " + flag);

		ContentValues values = new ContentValues();

		values.put(StrokeSQLiteHelper.COLUMN_FLAG, flag);
		// new String[]{id.toString()}
		mDatabase.update(StrokeSQLiteHelper.TABLE_VIDEOS, values, StrokeSQLiteHelper.COLUMN_VIDEO_ID + " = '" + id.toString()
				+ "'", null);
	}

	/**
	 * Updates the downloading progress of the video associated with the given
	 * UUID. Is called repeatedly, keep it simple and clean.
	 * 
	 * @param id
	 *            videoId
	 * @param progress
	 */
	public void updateVideoProgress(UUID id)
	{
		Log.d("VideoDataSource", "Update video progress id " + id);

		mDatabase.execSQL("UPDATE " + StrokeSQLiteHelper.TABLE_VIDEOS + " SET " + StrokeSQLiteHelper.COLUMN_PROGRESS + " =  "
				+ StrokeSQLiteHelper.COLUMN_PROGRESS + " + 1 WHERE " + StrokeSQLiteHelper.COLUMN_VIDEO_ID + " = ? ",
				new String[] { id.toString() });
	}

	/**
	 * Delete the video associated with the given UUID from the database.
	 * 
	 * @param id
	 *            videoId
	 */
	public void deleteVideo(UUID id)
	{
		Log.i("VideoDataSource", "Deleted Video with id " + id);

		mDatabase
				.delete(StrokeSQLiteHelper.TABLE_VIDEOS, StrokeSQLiteHelper.COLUMN_VIDEO_ID + " = '" + id.toString() + "'", null);
	}

	/**
	 * Delete all videos which have not yet been accepted for download by this
	 * doctor.
	 */
	public void deleteNonacceptedVideos()
	{
		Log.i("VideoDataSource", "Deleted nonaccepted videos");

		mDatabase.delete(StrokeSQLiteHelper.TABLE_VIDEOS, StrokeSQLiteHelper.COLUMN_FLAG + " = '" + Inbox.NOT_LOADED + "'", null);
	}

	/**
	 * Removes any video which was received more than thirty minutes ago from
	 * the database and returns a list with UUIDs of the removed videos.
	 * 
	 * @return list with UUIDs of the removed videos
	 */
	public List<UUID> cleanupOldVideos()
	{
		Long date = new Date().getTime();

		Cursor cursor = mDatabase.query(StrokeSQLiteHelper.TABLE_VIDEOS, new String[] { StrokeSQLiteHelper.COLUMN_VIDEO_ID },
				date + " - " + StrokeSQLiteHelper.COLUMN_DATE + " > " + THIRTY_MIN_AS_MS, null, null, null, null);

		List<UUID> list = new ArrayList<UUID>();

		cursor.moveToFirst();

		while (!cursor.isAfterLast())
		{
			UUID id = UUID.fromString(cursor.getString(0));
			list.add(id);
			cursor.moveToNext();
		}

		mDatabase.delete(StrokeSQLiteHelper.TABLE_VIDEOS, date + " - " + StrokeSQLiteHelper.COLUMN_DATE + " > "
				+ THIRTY_MIN_AS_MS, null);

		return list;
	}

	/**
	 * Selects all videos from the database and returns them.
	 * 
	 * @return List of all videos currently contained by the database.
	 */
	public List<Video> getAllVideos()
	{
		List<Video> videos = new ArrayList<Video>();

		Cursor cursor = mDatabase.query(StrokeSQLiteHelper.TABLE_VIDEOS, mAllColumns, null, null, null, null,
				StrokeSQLiteHelper.COLUMN_DATE + " DESC");
		cursor.moveToFirst();

		while (!cursor.isAfterLast())
		{
			Video video = cursorToVideo(cursor);
			videos.add(video);
			cursor.moveToNext();
		}

		cursor.close();

		return videos;
	}

	/**
	 * Selects all videos whose downloads have not been finished but already
	 * accepted for download by a doctor and returns them.
	 * 
	 * @return List of videos with unfinished downloads.
	 */
	public List<Video> getAllUnfinishedVideos()
	{
		List<Video> videos = new ArrayList<Video>();

		Cursor cursor = mDatabase.query(StrokeSQLiteHelper.TABLE_VIDEOS, mAllColumns, StrokeSQLiteHelper.COLUMN_FLAG + " = "
				+ Inbox.LOADING, null, null, null, null);

		cursor.moveToFirst();

		while (!cursor.isAfterLast())
		{
			Video video = cursorToVideo(cursor);
			videos.add(video);
			cursor.moveToNext();
		}

		cursor.close();

		return videos;
	}

	/**
	 * Converts a cursor pointing to one database row into a video object.
	 * 
	 * @param cursor
	 *            containing the results from a query
	 * @return New video object containing the data from the database row to
	 *         which the cursor points
	 */
	private Video cursorToVideo(Cursor cursor)
	{
		Video video = new Video();
		video.setId(cursor.getLong(0));
		video.setVideoId(cursor.getString(1));
		video.setDate(cursor.getLong(2));
		video.setFlag(cursor.getInt(3));
		video.setParts(cursor.getLong(4));
		video.setProgress(cursor.getLong(5));
		return video;
	}

}
